import base64
import json

import pymysql
import pymssql
import requests



"""
封装mysql操作
"""
class OpeartorDB(object):
    def __init__(self):
        """
        初始化方法，习惯性留着
        """
        pass

    def connectDB(self, **kwargs):
        """
        连接数据库
        :return: 返回成功失败，原因
        """
        host = kwargs['db']
        port = kwargs['dbport']
        user = kwargs['dbuser']
        pwd = kwargs['dbpwd']
        database = kwargs['dbname']
        #从环境变量里取
        if kwargs['type'] == 1:
            try:
                self.db = pymysql.connect(host=host, port=int(port), user=user, password=pwd, database=database, charset='utf8')
                return True, '连接数据成功'
            except Exception as e:
                return False, '连接数据失败【' + str(e) + '】'
        elif kwargs['type'] == 2:
            pass      #oracel
        elif kwargs['type'] == 4:
            try:
                self.db = pymssql.connect(host=host, user=user, password=pwd, database=database)
                return True, '连接数据库成功'
            except Exception as e:
                return False, '连接数据库失败【{}】'.format(str(e))



    def closeDB(self):
        """
        关闭数据连接，不关闭会导致数据连接数不能释放，影响数据库性能
        :return:
        """
        self.db.close()

    def excetSql(self, enpsql, **kwargs):
        """
        执行sql方法，
        :param enpsql: 传入的sql语句
        :return: 返回成功与执行结果 或 失败与失败原因
        """
        isOK, result = self.connectDB(**kwargs)
        if isOK is False:
            return isOK, result
        try:
            cursor = self.db.cursor()
            cursor.execute(enpsql)
            res = cursor.fetchone()     #为了自动化测试的速度，一般场景所以只取一条数据
            if res is not None and 'select' in enpsql.lower():
                des = ()
                for item in cursor.description:
                    des += (item[0],)
                result = dict(zip(des, res))
            elif res is None and ('insert' in enpsql.lower() or 'update' in enpsql.lower()):
                self.db.commit()
                result = ''
            cursor.close()
            self.closeDB()
            return True, result
        except Exception as e:
            return False, 'SQL执行失败,原因：[' + str(e) + ']'




    def get_ibmcbase64account(self, user, pwd):
        """
        传入ibmc账号，进行base64加密
        """
        str1 = '{}:{}'.format(user, pwd)
        bytesStr = str1.encode(encoding='utf-8')
        b64str = str(base64.b64encode(bytesStr), 'utf-8')
        return b64str

    def get_ibmcHeader(self, user, pwd):
        b64str = self.get_ibmcbase64account(user, pwd)
        header = {
            'Authorization': 'Basic {}'.format(b64str),
            'Content-Type': 'application/json',
            'Connection': 'close'
        }
        return header

    def get_ibmcdata(self, **kwargs):
        try:
            url = kwargs['db']
            user = kwargs['dbuser']
            pwd = kwargs['dbpwd']
            mdx = json.loads(kwargs['mdx'])
            header = self.get_ibmcHeader(user, pwd)
            result = requests.post(url=url, headers=header, json=mdx)
            return True, result.json()['Cells']
        except Exception as e:
            return False, str(e)




# lst_str = '12365464649849849849461613312'
# print(lst_str[0:10] + '...')
# kwars = {}
# kwars['db'] = 'http://172.56.20.48:10411/api/v1/ExecuteMDX?$expand=Cells($select=*)'
# kwars['dbuser'] = 'admin'
# kwars['dbpwd'] = 'apple'
# kwars['data'] = '{"MDX":"Select {[组织_楼栋].[集团].children} ON ROWS,{[产品].members} ON COLUMNS From [C0100_楼栋产品映射] Where ([情景].[启动会],[版本].[工作版])"}'
# word = 'Ordinal,FormatString,FormattedValue,Updateable'
# kwars['db'] = kwars['db'].replace('*', word)
# obj = OpeartorDB()
# result = obj.get_ibmcdata(**kwars)
# res = result.json()['Cells']
# lst_format = []
# for r in res:
#     lst_format.append(float(r['FormatString']))
#
# formatstring = str(lst_format)
# print(formatstring)

# a = 'aaaaa'
# b = 'aa,bb'
#
# print(a.split(','))
# print(b.split(','))

# import random
# p_header = """135、136、137、138、139、150、151、152、158、159、182 、183、184、187、188、147、178、130、131、132、155、156、185、186、145、176、180、181、189、133、153、177"""
# p_lst = p_header.split('、')
# fisrt = random.choice(p_lst)
# ps_lst = list('1234567890')
# sec = ''.join(random.sample(ps_lst, 8))
# fisrt += sec
# print(fisrt)







# try:
#     db = pymysql.connect(host='rm-wz9ih626njrt6yelujm.mysql.rds.aliyuncs.com', port=3306, user='xls_server_test', password='xls_server_test', database='xls_pay', charset='utf8')
#     print("linksuccess")
# except Exception as e:
#     print("linkfaile")

# sql = 'select * from Student'
# oper = OpeartorDB()
# isOK, result = oper.excetSql(sql)
# print(result)
# user = 'admin'
# pwd = 'apple'
# str1 = '{}:{}'.format(user, pwd)
# bytesStr = str1.encode(encoding='utf-8')
# b64str = str(base64.b64encode(bytesStr), 'utf-8')
# print(b64str)


# import pymssql
#
# server = '172.56.2.28'
# user = 'sa'
# password = 'sapi@0814'
# DB = 'EMCordova'
#
# conn = pymssql.connect(server, user, password, DB)
# cursor = conn.cursor()
# cursor.execute('select top 10 * from TEM_Acceptance')
# result = cursor.fetchone()
# print(result)
# print(cursor.description)

# import random
# f_params = 10
# ps_lst = list('1234567890')
# ranNo = ''.join(random.sample(ps_lst, f_params))
# print(ranNo)
# print(type(ranNo))


